﻿using Model;
using Model.CustomDataType;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class TreatmentDA: DBConnection
    {
        public TreatmentDA()
        {
            sqlCommand.Connection = sqlConnection;
        }

        public int addTreatment(Treatment treatment)
        {
            string query = "insert into " + Common.KHAM_BENH +
                " values(@patientid,@dateoftreatment,@weight,@treatmentstatusid,@symptom, @diagnosisid, @advice, "+
                "@dateofretreatment,@dayofusingmedicine,@workerid,@treatmentcost,@medicinecost,@paystatus); select SCOPE_IDENTITY();";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@patientid", treatment.getPatientId());
            sqlCommand.Parameters.AddWithValue("@dateoftreatment", Utils.convertCMDateTimeToDateTime(treatment.getDateOfTreatment()));
            sqlCommand.Parameters.AddWithValue("@weight", treatment.getWeight());
            sqlCommand.Parameters.AddWithValue("@treatmentstatusid", treatment.getTreatmentStatusId());
            sqlCommand.Parameters.AddWithValue("@symptom", treatment.getSymptom());
            sqlCommand.Parameters.AddWithValue("@diagnosisid", treatment.getDiagnosisId());
            sqlCommand.Parameters.AddWithValue("@advice", treatment.getAdvice());
            if (treatment.getDateOfReTreatment() == null)
            {
                sqlCommand.Parameters.AddWithValue("@dateofretreatment", null);
            }
            else
            {
                sqlCommand.Parameters.AddWithValue("@dateofretreatment", Utils.convertCMDateTimeToDateTime(treatment.getDateOfReTreatment()));
            }
            sqlCommand.Parameters.AddWithValue("@dayofusingmedicine", treatment.getDayOfUsingMedicine());
            sqlCommand.Parameters.AddWithValue("@workerid", treatment.getWorkerId());
            sqlCommand.Parameters.AddWithValue("@treatmentcost", treatment.getTreatmentCost());
            sqlCommand.Parameters.AddWithValue("@medicinecost", treatment.getMedicineCost());
            sqlCommand.Parameters.AddWithValue("@paystatus", treatment.getPayStatus());
            try
            {
                open();
                int idRe = -1;
                sqlCommand.CommandText = query;
                string re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return -1;
            }
        }
        public int getTreatmentCost()
        {
            string query = "select treatmentcost from " + Common.PARAM;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return -1;
                }
                int result = -1;
                while (sqlDataReader.Read())
                {
                    result = sqlDataReader.GetInt32(0);

                }
                close();
                return result;
            }
            catch
            {
                return -1;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        public bool updateTreatment(Treatment treatment)
        {
            string query = "update " + Common.KHAM_BENH +
                " set treatmentstatusid = @treatmentstatusid,symptom=@symptom, diagnosisid=@diagnosisid,"+
                "advice=@advice,dateofretreatment=@dateofretreatment,dayofusingmedicine=@dayofusingmedicine,"+
            "workerid=@workerid,treatmentcost=@treatmentcost,medicinecost=@medicinecost "+
            "where treatmentid=" +treatment.getTreatmentId();
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@treatmentstatusid", treatment.getTreatmentStatusId());
            sqlCommand.Parameters.AddWithValue("@symptom", treatment.getSymptom());
            sqlCommand.Parameters.AddWithValue("@diagnosisid", treatment.getDiagnosisId());
            sqlCommand.Parameters.AddWithValue("@advice", treatment.getAdvice());
            sqlCommand.Parameters.AddWithValue("@dateofretreatment", Utils.convertCMDateTimeToDateTime(treatment.getDateOfReTreatment()));
            sqlCommand.Parameters.AddWithValue("@dayofusingmedicine", treatment.getDayOfUsingMedicine());
            sqlCommand.Parameters.AddWithValue("@workerid", treatment.getWorkerId());
            sqlCommand.Parameters.AddWithValue("@treatmentcost", treatment.getTreatmentCost());
            sqlCommand.Parameters.AddWithValue("@medicinecost", treatment.getMedicineCost());
            try
            {
                open();
                sqlCommand.CommandText = query;
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        public bool updateTreatmentStatusId(int treatmentId, int newStatusId)
        {
            string query = "update " + Common.KHAM_BENH +
                " set treatmentstatusid = @treatmentstatusid " +
                "where treatmentid = " + treatmentId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@treatmentstatusid", newStatusId);

            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }

        public int getCurrentStatusId(int treatmentId)
        {
            string query = "select treatmentstatusid from " + Common.KHAM_BENH + " where treatmentid=" + treatmentId;
            sqlCommand.CommandText = query;
            try
            {

                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return -1;
                }
                int result = -1;
                while (sqlDataReader.Read())
                {
                    result = sqlDataReader.GetInt32(0); ;

                }
                close();
                return result;
            }
            catch
            {
                return -1;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public bool updatePayStatusId(int treatmentId, int newStatusId)
        {
            string query = "update " + Common.KHAM_BENH +
                " set paystatus = @paystatus " +
                "where treatmentid = " + treatmentId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@paystatus", newStatusId);

            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }
        public short getCurrentPayStatusId(int treatmentId)
        {
            string query = "select paystatus from " + Common.KHAM_BENH + " where treatmentid=" + treatmentId;
            sqlCommand.CommandText = query;
            try
            {

                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return -1;
                }
                short result = -1;
                while (sqlDataReader.Read())
                {
                    result = sqlDataReader.GetInt16(0);

                }
                close();
                return result;
            }
            catch
            {
                return -1;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public Treatment getTreatmentById(int treatmentID)
        {
            string query = "select * from " + Common.KHAM_BENH + " where treatmentid=" + treatmentID;
            sqlCommand.CommandText = query;
            try
            {

                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                Treatment treatment = new Treatment();
                while (sqlDataReader.Read())
                {
                    treatment = createTreatmentFromReader(sqlDataReader);

                }
                close();
                return treatment;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public Treatment[] getAllTreatment()
        {
            string query = "select * from " + Common.KHAM_BENH;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Treatment> liTreatment = new List<Treatment>();
                Treatment treatment = new Treatment();
                while (sqlDataReader.Read())
                {
                    treatment = createTreatmentFromReader(sqlDataReader);
                    liTreatment.Add(treatment);
                }
                close();
                return liTreatment.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public Treatment[] getTreatmentByDateOfTreament(CMDateTime date)
        {
            string query = "select * from " + Common.KHAM_BENH +
                " where dateoftreatment = '" + Utils.convertCMDateTimeToDateTime(date) + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Treatment> liTreatment = new List<Treatment>();
                Treatment treatment = new Treatment();
                while (sqlDataReader.Read())
                {
                    treatment = createTreatmentFromReader(sqlDataReader);
                    liTreatment.Add(treatment);
                }
                close();
                return liTreatment.ToArray();
            }
            catch
            {
                //log here
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public Treatment[] getTreatmentByRawQuery(string query)
        {
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Treatment> liTreatment = new List<Treatment>();
                Treatment treatment = new Treatment();
                while (sqlDataReader.Read())
                {
                    string str = "";
                    for (int i = 0; i < sqlDataReader.FieldCount; i++)
                    {
                        str = sqlDataReader.GetName(i);//Get name of column;

                        str.ToLower();
                        switch (str)
                        {
                            case "treatmentid":
                                treatment.setTreatmentId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "patientid":
                                treatment.setPatientId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "dateoftreatment":
                                treatment.setDateOfTreatment(Utils.convertDateTimeToCMDateTime((DateTime)sqlDataReader.GetValue(i)));
                                break;
                            case "weight":
                                treatment.setWeight((float)sqlDataReader.GetValue(i));
                                break;
                            case "treatmentstatusid":
                                treatment.setTreatmentStatusId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "symptom":
                                treatment.setSymptom(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "diagnosisid":
                                treatment.setDiagnosisId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "advice":
                                treatment.setAdvice(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "dateofretreatment":
                                treatment.setDateOfReTreatment(Utils.convertDateTimeToCMDateTime((DateTime)sqlDataReader.GetValue(i)));
                                break;
                            case "dayofusingmedicine":
                                treatment.setDayOfUsingMedicine(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "workerid":
                                treatment.setWorkerId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "treatmentcost":
                                treatment.setTreatmentCost(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicinecost":
                                treatment.setMedicineCost(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "paystatus":
                                treatment.setPayStatus(Int16.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                        }
                    }
                    liTreatment.Add(treatment);
                }
                close();
                return liTreatment.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        private Treatment createTreatmentFromReader(SqlDataReader rd)
        {

            if (rd != null && !rd.IsClosed)
            {
                Treatment treatment = new Treatment();
                treatment.setTreatmentId(rd.GetInt32(0));
                treatment.setPatientId(rd.GetInt32(1));
                treatment.setDateOfTreatment(Utils.convertDateTimeToCMDateTime(rd.GetDateTime(2)));
                treatment.setWeight(rd.GetFloat(3));
                treatment.setTreatmentStatusId(rd.GetInt32(4));
                treatment.setSymptom(rd.GetString(5));
                treatment.setDiagnosisId(rd.GetInt32(6));
                treatment.setAdvice(rd.GetString(7));
                treatment.setDateOfReTreatment(Utils.convertDateTimeToCMDateTime(rd.GetDateTime(8)));
                treatment.setDayOfUsingMedicine(rd.GetInt32(9));
                treatment.setWorkerId(rd.GetInt32(10));
                treatment.setTreatmentCost(rd.GetInt32(11));
                treatment.setMedicineCost(rd.GetInt32(12));
                treatment.setPayStatus(rd.GetInt16(13));
                return treatment;
            }
            else
            {
                return null;
            }

        }
    
        ////////////////////////////////////////////////////
        //
        // Treatment status
        //
        ////////////////////////////////////////////////////

        public bool addTreatmentStatus(int name)
        {
            string query = "insert into " + Common.TINH_TRANG_KHAM_BENH +
                " values(@treatmentstatusname)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@treatmentstatusname", name);
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        ////////////////////////////////////////////////////
        //
        // Treatment Advice
        //
        ////////////////////////////////////////////////////

        public bool addAdvice(string name)
        {
            string query = "insert into " + Common.LOI_DAN +
                " values(@advicename)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@advicename", name);
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        public bool updateAdvice(int id, string name)
        {
            string query = "update " + Common.LOI_DAN +
                " set advicename = @advicename where adviceid = " + id;
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@advicename", name);
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        ////////////////////////////////////////////////////
        //
        // Treatment Symptom
        //
        ////////////////////////////////////////////////////

        public bool addSymptom(string name)
        {
            string query = "insert into " + Common.TRIEU_CHUNG +
                " values(@symptomname)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@symptomname", name);
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        public bool updateSymptom(int id, string name)
        {
            string query = "update " + Common.TRIEU_CHUNG +
                " set symptomname = @symptomname where symptomid = " + id;
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@symptomname", name);
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        ////////////////////////////////////////////////////
        //
        // Treatment diagnosis
        //
        ////////////////////////////////////////////////////

        public bool addDiagnosis(string name)
        {
            string query = "insert into " + Common.CHAN_DOAN +
                " values(@diagnosisname)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@diagnosisname", name);
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        public bool updateDiagnosis(int id, string name)
        {
            string query = "update " + Common.CHAN_DOAN +
                " set diagnosisname = @diagnosisname where diagnosisid = " + id;
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@diagnosisname", name);
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        public string[] getAllDiagnosis()
        {
            string query = "select * from " + Common.CHAN_DOAN;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<string> liDia = new List<String>();
                string value = "";
                while (sqlDataReader.Read())
                {
                    value = sqlDataReader.GetString(1);
                    liDia.Add(value);
                }
                close();
                return liDia.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public int getDiagnosisIdByName(string name)
        {
            string query = "select * from " + Common.CHAN_DOAN +
                " where diagnosisname = N'" + name + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return -1;
                }
                int value = -1;
                while (sqlDataReader.Read())
                {
                    value = sqlDataReader.GetInt32(0);
                }
                close();
                return value;
            }
            catch
            {
                return -1;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public bool isDiagnosisExist(string name)
        {
            string query = "select diagnosisname from " + Common.CHAN_DOAN +
                " where diagnosisname = N'" + name + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        ////////////////////////////////////////////////////
        //
        // Treatment detail
        //
        ////////////////////////////////////////////////////

        public bool addTreatmentDetail(TreatmentDetail treatmentDe)
        {
            string query = "insert into " + Common.CHI_TIET_KHAM_BENH +
                " values(@treatmentid,@medicineid,@medicinemorning,@medicinenoon, @medicineafternoon, @medicineevening, " +
                "@quantitytotal,@dosage,@money)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@treatmentid", treatmentDe.getTreatmentId());
            sqlCommand.Parameters.AddWithValue("@medicineid", treatmentDe.getMedicinetId());
            sqlCommand.Parameters.AddWithValue("@medicinemorning", treatmentDe.getMorningMed());
            sqlCommand.Parameters.AddWithValue("@medicinenoon", treatmentDe.getNoonMed());
            sqlCommand.Parameters.AddWithValue("@medicineafternoon", treatmentDe.getAfternoonMed());
            sqlCommand.Parameters.AddWithValue("@medicineevening", treatmentDe.getEveningMed());
            sqlCommand.Parameters.AddWithValue("@quantitytotal", treatmentDe.getQuantity());
            sqlCommand.Parameters.AddWithValue("@dosage", treatmentDe.getDosage());
            sqlCommand.Parameters.AddWithValue("@money", treatmentDe.getMoney());
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }
        }

        public bool updateTreatmentDetail(TreatmentDetail treatmentDe)
        {
            //log here
            int treDeId = treatmentDe.getDetailId();
            string query = "update " + Common.CHI_TIET_KHAM_BENH +
                " set treatmentid = @treatmentid, " +
                "medicineid = @medicineid,medicinemorning = @medicinemorning,medicinenoon = @medicinenoon,"+
                "medicineafternoon = @medicineafternoon," +
                "medicineevening = @medicineevening, quantitytotal = @quantitytotal " +
                "dosage = @dosage, money = @money " +
                "where treatmentdetailid = " + treDeId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@treatmentid", treatmentDe.getTreatmentId());
            sqlCommand.Parameters.AddWithValue("@medicineid", treatmentDe.getMedicinetId());
            sqlCommand.Parameters.AddWithValue("@medicinemorning", treatmentDe.getMorningMed());
            sqlCommand.Parameters.AddWithValue("@medicinenoon", treatmentDe.getNoonMed());
            sqlCommand.Parameters.AddWithValue("@medicineafternoon", treatmentDe.getAfternoonMed());
            sqlCommand.Parameters.AddWithValue("@medicineevening", treatmentDe.getEveningMed());
            sqlCommand.Parameters.AddWithValue("@quantitytotal", treatmentDe.getQuantity());
            sqlCommand.Parameters.AddWithValue("@dosage", treatmentDe.getDosage());
            sqlCommand.Parameters.AddWithValue("@money", treatmentDe.getMoney());
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }
        public TreatmentDetail getTreatmentDetailById(int treatmentDeID)
        {
            string query = "select * from " + Common.CHI_TIET_KHAM_BENH + " where treatmentdetailid=" + treatmentDeID;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                TreatmentDetail treatment = new TreatmentDetail();
                while (sqlDataReader.Read())
                {
                    treatment = createTreatmentDetailFromReader(sqlDataReader);

                }
                close();
                return treatment;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        public TreatmentDetail[] getAllTreatmentDetail()
        {
            string query = "select * from " + Common.CHI_TIET_KHAM_BENH;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<TreatmentDetail> liTreatmentDe = new List<TreatmentDetail>();
                TreatmentDetail treatmentDe = new TreatmentDetail();
                while (sqlDataReader.Read())
                {
                    treatmentDe = createTreatmentDetailFromReader(sqlDataReader);
                    liTreatmentDe.Add(treatmentDe);
                }
                close();
                return liTreatmentDe.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        
        public TreatmentDetail[] getTreatmentDetailByRawQuery(string query)
        {
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<TreatmentDetail> liTreatmentDe = new List<TreatmentDetail>();
                TreatmentDetail treatmentDe = null;
                while (sqlDataReader.Read())
                {
                    string str = "";
                    treatmentDe = new TreatmentDetail();
                    for (int i = 0; i < sqlDataReader.FieldCount; i++)
                    {
                        str = sqlDataReader.GetName(i);//Get name of column;

                        str.ToLower();
                        switch (str)
                        {
                            case "treatmentdetailid":
                                treatmentDe.setDetailId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "treatmentid":
                                treatmentDe.setTreatmentId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicineid":
                                treatmentDe.setMedicinetId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicinemorning":
                                treatmentDe.setMorningMed(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicinenoon":
                                treatmentDe.setNoonMed(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicineafternoon":
                                treatmentDe.setAfternoonMed(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicineevening":
                                treatmentDe.setEveningMed(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "quantitytotal":
                                treatmentDe.setQuantity(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "dosage":
                                treatmentDe.setDosage(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "money":
                                treatmentDe.setMoney(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                        }
                    }
                    liTreatmentDe.Add(treatmentDe);
                }
                close();
                return liTreatmentDe.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        private TreatmentDetail createTreatmentDetailFromReader(SqlDataReader rd)
        {
            if (rd != null && !rd.IsClosed)
            {
                TreatmentDetail treatmentDe = new TreatmentDetail();
                treatmentDe.setDetailId(rd.GetInt32(0));
                treatmentDe.setTreatmentId(rd.GetInt32(1));
                treatmentDe.setMedicinetId(rd.GetInt32(2));
                treatmentDe.setMorningMed(rd.GetInt32(3));
                treatmentDe.setNoonMed(rd.GetInt32(4));
                treatmentDe.setAfternoonMed(rd.GetInt32(5));
                treatmentDe.setEveningMed(rd.GetInt32(6));
                treatmentDe.setQuantity(rd.GetInt32(7));
                treatmentDe.setDosage(rd.GetString(8));
                treatmentDe.setMoney(rd.GetInt32(9));
                return treatmentDe;
            }
            else
            {
                return null;
            }

        }
       
    }
}
